﻿/******************************************************/
// DALPost データアクセスクラス  
/******************************************************/

using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using MAA.Entity;

namespace MAA.Data
{
    /// <summary>
    /// ポストを参照のデータベースへのプロセスデータアクセス 
    /// </summary>
    public class DALPost
    {
        private string conStr = ConfigurationManager.AppSettings["connectionString"];

        /// <summary>
        /// ポストは、検索条件を入手Follow 
        /// </summary>
        /// <param name="sortType"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="riceID"></param>
        /// <param name="provinceID"></param>
        /// <param name="priceMin"></param>
        /// <param name="priceMax"></param>
        /// <param name="rowNum"></param>
        /// <returns></returns>
        public DataTable getPost(string lang, string sortType, int pageIndex, int pageSize, int riceID, short provinceID, int priceMin, int priceMax, out long rowNum)
        {
            DataTable tblPost = new DataTable();
            rowNum = 0;

            SqlConnection conn = new SqlConnection(conStr);
            SqlCommand cmd;
            if (sortType == "Time")
            {
                cmd = new SqlCommand("SearchPostOrderByTime", conn);
            }
            else
            {
                cmd = new SqlCommand("SearchPostOrderByPrice", conn);
            }
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@Lang", SqlDbType.VarChar).Value = lang;
            cmd.Parameters.Add("@PageIndex", SqlDbType.Int).Value = pageIndex;
            cmd.Parameters.Add("@PageSize", SqlDbType.Int).Value = pageSize;
            cmd.Parameters.Add("@riceID", SqlDbType.Int).Value = riceID;
            cmd.Parameters.Add("@provinceID", SqlDbType.TinyInt).Value = provinceID;
            cmd.Parameters.Add("@Minprice", SqlDbType.Int).Value = priceMin;
            cmd.Parameters.Add("@Maxprice", SqlDbType.Int).Value = priceMax;

            cmd.Parameters.Add("@RecordCount", SqlDbType.BigInt);
            cmd.Parameters["@RecordCount"].Direction = ParameterDirection.Output;

            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(tblPost);
            rowNum = Convert.ToInt64(cmd.Parameters["@RecordCount"].Value);
            conn.Close();

            return tblPost;
        }

        /// <summary>
        /// ユーザのすべての投稿を取得 
        /// </summary>
        /// <param name="userID"></param>
        /// <returns></returns>
        public DataTable getPost(string lang, int pageIndex, int pageSize, long userID, int riceID, short provinceID, short statusID, out long rowNum)
        {
            DataTable tblPost = new DataTable();
            rowNum = 0;

            SqlConnection conn = new SqlConnection(conStr);
            SqlCommand cmd;
            cmd = new SqlCommand("GetTransactionPost", conn);

            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@Lang", SqlDbType.VarChar).Value = lang;
            cmd.Parameters.Add("@PageIndex", SqlDbType.Int).Value = pageIndex;
            cmd.Parameters.Add("@PageSize", SqlDbType.Int).Value = pageSize;
            cmd.Parameters.Add("@userID", SqlDbType.BigInt).Value = userID;
            cmd.Parameters.Add("@riceID", SqlDbType.Int).Value = riceID;
            cmd.Parameters.Add("@provinceID", SqlDbType.TinyInt).Value = provinceID;
            cmd.Parameters.Add("@statusID", SqlDbType.TinyInt).Value = statusID;

            cmd.Parameters.Add("@RecordCount", SqlDbType.BigInt);
            cmd.Parameters["@RecordCount"].Direction = ParameterDirection.Output;

            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(tblPost);
            rowNum = Convert.ToInt64(cmd.Parameters["@RecordCount"].Value);
            conn.Close();

            return tblPost;
        }

        /// <summary>
        /// 利用者の投稿はお米の種類に対応して取得 
        /// </summary>
        /// <param name="userID"></param>
        /// <param name="riceID"></param>
        /// <returns></returns>
        public DataTable getPost(long userID, int riceID)
        {
            DataTable tblNumPost = new DataTable();

            // String query to get number of post by riceID and userID
            string sql = @" SELECT  COUNT(Post.ID) AS postNum
                            FROM    Post
                            WHERE   Post.UserID = @userID
                                AND	Post.RiceID = @riceID
                                AND	Post.[Status] = 1";

            SqlConnection conn = new SqlConnection(conStr);
            SqlCommand cmd = new SqlCommand(sql, conn);

            cmd.Parameters.Add("@userID", SqlDbType.BigInt).Value = userID;
            cmd.Parameters.Add("@riceID", SqlDbType.Int).Value = riceID;

            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(tblNumPost);
            conn.Close();

            return tblNumPost;
        }

        /// <summary>
        /// ポストの詳細情報を取得します 
        /// </summary>
        /// <param name="postID"></param>
        /// <returns></returns>
        public DataTable getPostInfo(string lang, long postID)
        {
            DataTable postDataTable = new DataTable();
            SqlDataAdapter postAdapter = new SqlDataAdapter();
            postAdapter.SelectCommand = new SqlCommand();

            if (lang.Equals("vi"))
            {
                postAdapter.SelectCommand.CommandText = @"
                SELECT 		Post.UserID,
                            Post.RiceID,
			                Rice.TypeName,
			                Post.Amount, 
			                Post.TradedAmount,
			                Post.MinOrderAmount,
			                Post.Price,
			                Post.PercentPrice,
			                Post.[Address],
			                Province.Name AS Province,
			                Post.CreateDate,
			                Post.[Status],
			                CONVERT(varchar, Post.CreateDate, 105) AS UpdateTime
                FROM		Post, Rice, Province
                WHERE 		Rice.ID = Post.RiceID  
	                AND 	Province.ID = Post.ProvinceID
	                AND		Post.ID = @postID";
            }
            else
            {
                postAdapter.SelectCommand.CommandText = @"
                SELECT 		Post.UserID,
                            Post.RiceID,
			                Rice.TypeNameJP AS TypeName,
			                Post.Amount, 
			                Post.TradedAmount,
			                Post.MinOrderAmount,
			                Post.Price,
			                Post.PercentPrice,
			                Post.[Address],
			                Province.NameJP AS Province,
			                Post.CreateDate,
			                Post.[Status],
			                CONVERT(varchar, Post.CreateDate, 105) AS UpdateTime
                FROM		Post, Rice, Province
                WHERE 		Rice.ID = Post.RiceID  
	                AND 	Province.ID = Post.ProvinceID
	                AND		Post.ID = @postID";
            }

            postAdapter.SelectCommand.Parameters.Add("@postID", SqlDbType.BigInt).Value = postID;

            postAdapter.SelectCommand.Connection = new SqlConnection(conStr);
            postAdapter.Fill(postDataTable);
            postAdapter.SelectCommand.Connection.Close();

            return postDataTable;
        }

        /// <summary>
        /// ポストの詳細情報は、順番に対応ゲット 
        /// </summary>
        /// <param name="orderID"></param>
        /// <returns></returns>
        public DataTable getPostOrder(string lang, long orderID)
        {
            DataTable postOrderDataTable = new DataTable();
            SqlDataAdapter postOrderAdapter = new SqlDataAdapter();
            postOrderAdapter.SelectCommand = new SqlCommand();

            if (lang.Equals("vi"))
            {
                postOrderAdapter.SelectCommand.CommandText = @"
                SELECT		Post.ID, 
			                Rice.TypeName,
			                Post.Amount,
			                Post.TradedAmount,
			                Post.Price,
                            Post.PercentPrice,
			                Post.[Address],
			                Province.Name AS Province
                FROM		Post, Rice, [Order], Province
                WHERE		Post.ID = [Order].PostID
	                AND		Rice.ID = Post.RiceID
	                AND		Province.ID = Post.ProvinceID
	                AND		[Order].ID = @orderID";
            }
            else
            {
                postOrderAdapter.SelectCommand.CommandText = @"
                SELECT		Post.ID, 
			                Rice.TypeNameJP AS TypeName,
			                Post.Amount,
			                Post.TradedAmount,
			                Post.Price,
                            Post.PercentPrice,
			                Post.[Address],
			                Province.NameJP AS Province
                FROM		Post, Rice, [Order], Province
                WHERE		Post.ID = [Order].PostID
	                AND		Rice.ID = Post.RiceID
	                AND		Province.ID = Post.ProvinceID
	                AND		[Order].ID = @orderID";
            }
            postOrderAdapter.SelectCommand.Parameters.Add("@orderID", SqlDbType.BigInt).Value = orderID;

            postOrderAdapter.SelectCommand.Connection = new SqlConnection(conStr);
            postOrderAdapter.Fill(postOrderDataTable);
            postOrderAdapter.SelectCommand.Connection.Close();

            return postOrderDataTable;
        }

        /// <summary>
        /// 新しい投稿を追加 
        /// </summary>
        /// <param name="post"></param>
        /// <returns></returns>
        public void insertPost(Post post)
        {
            // String query to insert a post into databse
            string sql = @"INSERT INTO Post (UserID,
                                            RiceID,
                                            Amount,
                                            TradedAmount,
                                            MinOrderAmount,
                                            Price,
                                            PercentPrice,
                                            [Address],
                                            ProvinceID,
                                            CreateDate,
                                            [Status])
                                    VALUES (@userID,
                                            @riceID,
                                            @amount,
                                            @tradedAmount,
                                            @minOrderAmount,
                                            @price,
                                            @percentPrice,
                                            @address,
                                            @provinceID,
                                            @date,
                                            @status)";

            SqlConnection conn = new SqlConnection(conStr);
            conn.Open();
            SqlCommand cmd = new SqlCommand(sql, conn);

            cmd.Parameters.Add("@userID", SqlDbType.BigInt).Value = post.UserID;
            cmd.Parameters.Add("@riceID", SqlDbType.Int).Value = post.RiceID;
            cmd.Parameters.Add("@amount", SqlDbType.BigInt).Value = post.Amount;
            cmd.Parameters.Add("@tradedAmount", SqlDbType.BigInt).Value = post.TradedAmount;
            cmd.Parameters.Add("@minOrderAmount", SqlDbType.Int).Value = post.MinOrderAmount;
            cmd.Parameters.Add("@price", SqlDbType.Int).Value = post.Price;
            cmd.Parameters.Add("@percentPrice", SqlDbType.TinyInt).Value = post.PercentPrice;
            cmd.Parameters.Add("@address", SqlDbType.NVarChar).Value = post.Address;
            cmd.Parameters.Add("@provinceID", SqlDbType.TinyInt).Value = post.ProvinceID;
            cmd.Parameters.Add("@date", SqlDbType.Date).Value = post.Date;
            cmd.Parameters.Add("@status", SqlDbType.TinyInt).Value = post.Status;

            cmd.ExecuteNonQuery();
            conn.Close();
        }

        /// <summary>
        /// ポストの更新状況 
        /// </summary>
        /// <param name="postID"></param>
        /// <param name="status"></param>
        /// <returns></returns>
        public void updatePost(long postID, short status)
        {
            SqlDataAdapter postAdapter = new SqlDataAdapter();
            postAdapter.UpdateCommand = new SqlCommand();

            postAdapter.UpdateCommand.CommandText = @"
                UPDATE		Post
                SET			Post.[Status] = @status
                WHERE		Post.ID = @postID";

            postAdapter.UpdateCommand.Parameters.Add("@postID", SqlDbType.BigInt).Value = postID;
            postAdapter.UpdateCommand.Parameters.Add("@status", SqlDbType.SmallInt).Value = status;

            postAdapter.UpdateCommand.Connection = new SqlConnection(conStr);
            postAdapter.UpdateCommand.Connection.Open();
            postAdapter.UpdateCommand.ExecuteNonQuery();
            postAdapter.UpdateCommand.Connection.Close();
        }

        /// <summary>
        /// ポストのアップデート取引量 
        /// </summary>
        /// <param name="postID"></param>
        /// <param name="tradedAmount"></param>
        /// <returns></returns>
        public void updatePost(long postID, long tradedAmount)
        {
            SqlDataAdapter postAdapter = new SqlDataAdapter();
            postAdapter.UpdateCommand = new SqlCommand();

            postAdapter.UpdateCommand.CommandText = @"
                UPDATE		Post
                SET			Post.TradedAmount = @tradedAmount
                WHERE		Post.ID = @postID";

            postAdapter.UpdateCommand.Parameters.Add("@postID", SqlDbType.BigInt).Value = postID;
            postAdapter.UpdateCommand.Parameters.Add("@tradedAmount", SqlDbType.BigInt).Value = tradedAmount;

            postAdapter.UpdateCommand.Connection = new SqlConnection(conStr);
            postAdapter.UpdateCommand.Connection.Open();
            postAdapter.UpdateCommand.ExecuteNonQuery();
            postAdapter.UpdateCommand.Connection.Close();
        }

        /// <summary>
        /// 投稿を削除
        /// </summary>
        /// <param name="postID"></param>
        /// <returns></returns>
        public void deletePost(long postID)
        {
            SqlDataAdapter postAdapter = new SqlDataAdapter();
            postAdapter.DeleteCommand = new SqlCommand();

            postAdapter.DeleteCommand.CommandText = @"
                DELETE FROM Post			
                WHERE		Post.ID = @postID";

            postAdapter.DeleteCommand.Parameters.Add("@postID", SqlDbType.BigInt).Value = postID;

            postAdapter.DeleteCommand.Connection = new SqlConnection(conStr);
            postAdapter.DeleteCommand.Connection.Open();
            postAdapter.DeleteCommand.ExecuteNonQuery();
            postAdapter.DeleteCommand.Connection.Close();
        }
    }
}